package com.ztesoft.report.controller;


import com.alibaba.fastjson.JSONObject;
import com.ztesoft.report.base.controller.BaseController;
import com.ztesoft.report.base.pojo.R;
import com.ztesoft.report.dao.ReportDataFileUploadDao;
import com.ztesoft.report.engine.exception.NotFoundLayoutColumnException;
import com.ztesoft.report.engine.exception.QueryParamsException;
import com.ztesoft.report.engine.exception.SQLQueryException;
import com.ztesoft.report.engine.exception.TemplatePraseException;
import com.ztesoft.report.engine.form.BootstrapQueryFormView;
import com.ztesoft.report.engine.util.ReportUtils;
import com.ztesoft.report.entity.ReportDto;
import com.ztesoft.report.exception.RException;
import com.ztesoft.report.service.ReportService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;

/**
 * <p>
 * 报表信息表 前端控制器
 * </p>
 *
 * @author y3tu
 * @since 2018-05-29
 */
@RestController
@RequestMapping("/report")
@Slf4j
public class ReportController extends BaseController {

    @Autowired
    ReportService reportService;

    @Autowired
    ReportDataFileUploadDao reportDataFileUploadDao;

    @Value("${com.ztesoft.createExcelPath}")
    private String createExcelPath;
    //分页插入Excel的大小
    @Value("${com.ztesoft.intoExcelOnceRows}")
    private Integer pageSize;

    @RequestMapping("/preview")
    public Object preview(@RequestBody String id, final HttpServletRequest request) {
        Map map = ReportUtils.renderByTemplate(id, new BootstrapQueryFormView(), request);
        return R.ok(map);
    }

    /**
     * 获取表格报表JSON格式数据
     *
     * @param request
     * @return
     */
    @ResponseBody
    @PostMapping(value = "/table/getData")
    public JSONObject getTableData(@RequestBody final String id, final HttpServletRequest request) {
        final JSONObject data = new JSONObject();
        try {
            ReportUtils.generate(id, data, request);
        } catch (QueryParamsException | NotFoundLayoutColumnException | SQLQueryException | TemplatePraseException ex) {
            data.put("htmlTable", ex.getMessage());
            log.error("报表生成失败", ex);
        } catch (final Exception ex) {
            data.put("htmlTable", "报表系统错误:" + ex.getMessage());
            log.error("报表系统出错", ex);
        }

        return data;
    }

    @RequestMapping("/queryMid")
    public Object queryMid(@RequestBody Map<String, Map<String, Object>> map) {
        StringBuilder sql = new StringBuilder();
        Map<String, Object> infoMidMap = map.get("paramMid");
        //分页处理
        int current = 0;
        int size = 0;
        int dsID = 1;
        String header = "";
        String meta = "";
        for (Map.Entry<String, Object> entry : infoMidMap.entrySet()) {
            if ("tableSql".equals(entry.getKey())) {
                sql.append(entry.getValue() + "  where 1=1 ");
            } else if ("currenT".equals(entry.getKey())) {
                current = Integer.parseInt(entry.getValue().toString());
            } else if ("sizE".equals(entry.getKey())) {
                size = Integer.parseInt(entry.getValue().toString());
            } else if ("dsiD".equals(entry.getKey())) {
                dsID = Integer.parseInt(entry.getValue().toString());
            } else if ("headeR".equals(entry.getKey())) {
                header = String.valueOf(entry.getValue());
            } else if ("metaColumnS".equals(entry.getKey())) {
                meta = String.valueOf(entry.getValue());
            }
        }
        infoMidMap.remove("tableSql");
        infoMidMap.remove("currenT");
        infoMidMap.remove("sizE");
        infoMidMap.remove("dsiD");
        infoMidMap.remove("headeR");
        infoMidMap.remove("metaColumnS");
        return reportService.queryMid(sql, infoMidMap, current, size, dsID, header,meta);
    }

    @RequestMapping("/searchMidBill")
    public Object searchMidBill(@RequestBody Map map) {
        return reportService.searchMidBill(map);
    }

    /**
     * 前台页面导出按钮
     * @param map  页面传递数据map
     * @param request
     * @param response
     * @return
     * @throws UnsupportedEncodingException
     */
    @RequestMapping("/createMidExcel")
    public R createMidExcel(@RequestBody Map<String, Map<String, Object>> map, HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException {
        StringBuffer sql = new StringBuffer();
        Map<String, Object> infoMidMap = map.get("paramMid");

        int reportId= (int) infoMidMap.get("reportId");
        //初始化年月日，如果不传入账期，默认当前年月日
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String yyyyMMdd = sdf.format(new Date());
        String year = yyyyMMdd.substring(0,4);
        String month = yyyyMMdd.substring(4,6);
        String day = yyyyMMdd.substring(6,8);
        boolean flag = false;

        for (Map.Entry<String, Object> entry : infoMidMap.entrySet()) {
            if ("tableSql".equals(entry.getKey())) {
                sql.append(entry.getValue() + "  where 1=1 ");
            }
            if (entry.getKey().endsWith("5")) {
                try {
                    Integer.parseInt(entry.getValue().toString());
                } catch (Exception e) {
                    //由于是拼接的，有可能某字段也是以5结尾，然后取出来的值，并不是前台账期传入的日期
                    //如果不是账期传入的日期，这里进行数字转换必然报错，所以捕捉一下，如果不报错就是正常传入的账期
                    continue;
                }
                //前台传入账期必然是yyyyMM形式
                String cycle = entry.getValue().toString();
                year = cycle.substring(0,4);
                month = cycle.substring(4,6);
            }
            if (entry.getKey().equals("BILL_MONTH5")) {
                if (entry.getValue() == null || entry.getValue() == "") {
                    flag = true;
                }
            }
        }
        if (flag) {
            infoMidMap.remove("BILL_MONTH5");
        }
        infoMidMap.remove("tableSql");
        infoMidMap.remove("reportId");
        infoMidMap.remove("currenT");
        infoMidMap.remove("sizE");
        infoMidMap.remove("dsiD");
        infoMidMap.remove("headeR");
        infoMidMap.remove("metaColumnS");

        //拼接sql
        this.createSql(sql,infoMidMap);

        log.info("查询SQL为："+sql);

        ReportDto reportDto = reportDataFileUploadDao.selectReportById(reportId+"");

        reportDto.setSqlText(new String(sql));

        //先行替换标题中带有的年月日,第二个参数这里用不上
        String fileName = changeData(reportDto.getExcelTitle() + ".xls","",year,month,day);

        String createPath = createExcelPath + "/" + fileName;
        // 3.根据service_interface表把status中00E更新为00F 传入的service_interface对象已经修改为00F
        // 4.根据report表的 Sql_text，meta_columns，header生成报表excel
        try {
            //会把表头最后行号返回回来，方便下面从第几行开始去合并单元格
            Integer rowNum = createExcel(reportDto, createExcelPath, fileName,year+month+day);
            //4.5 根据生成好的EXCEL添加下面的制表部门等数据
            hanldeExcel(reportDto, createPath, fileName, rowNum);
        } catch (Exception e) {
            e.printStackTrace();
           throw new RException(e.getMessage());
        }
        //String createPath=createExcelPath + "/" + testFile;

        Map map1=new HashMap();
        map1.put("filename",fileName);
        map1.put("createPath",createPath);
        map1.put("uuid", UUID.randomUUID().toString());

        return R.ok(map1);

    }

    /**
     * 把筛选条件添加到默认sql之后
     * @param sql
     * @param map
     */
    public void createSql(StringBuffer sql, Map<String, Object> map) {
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            if (entry.getValue() != null && entry.getValue() != "") {
                String value = entry.getValue().toString();
                String key = entry.getKey();//key里面包含了字段已经type，比如 A1 代表字段A 类型1
                String field = key.substring(0, key.length() - 1);//字段
                String type = key.substring(key.length() - 1);//类型
                switch (type) {
                    case "1":
                        sql.append(" and " + field + " like '%" + value + "%' ");
                        break;
                    case "2":
                        String[] date = handleDate(value);
                        sql.append(" and " + field + " >= to_date(" + date[0] + ",'yyyyMMdd') and " + field + " <= to_date(" + date[1] + ",'yyyyMMdd')");
                        break;
                    case "3":
                        sql.append(" and " + field + " = '" + value + "'");
                        break;
                    case "4":
                        sql.append(" and " + field + " = '" + value + "'");
                        break;
                    case "5":
                        sql.append(" and " + field + " = '" + value + "'");
                        break;
                }

            }
        }
    }

    /**
     * 替换一些字符串
     * @param oldData
     * @param newData
     * @param year
     * @param month
     * @param day
     * @return
     */
    public String changeData(String oldData,String newData,String year,String month,String day) {
        //理论上来说替换会一次全部替换完，如果出现不能替换的会出现死循环，所以count代表次数，如果一次没替换完，说明存在错误替换字符
        int count = 0;
        String nowTime = new SimpleDateFormat("yyyyMMdd").format(new Date());
        while (oldData.contains("#{{")) {
            if (count > 1) {
                throw new RException("存在错误的替换字符，不在#{{YYYY}}、#{{MM}}、#{{DD}}行列");
            }
            oldData = oldData.replace("#{{TITLE}}",newData);
            oldData = oldData.replace("#{{YYYY}}",year);
            oldData = oldData.replace("#{{MM}}",month);
            oldData = oldData.replace("#{{DD}}",day);
            oldData = oldData.replace("#{{YYYYMMDD}}",nowTime);
            count++;
        }
        return oldData;
    }


    /**
     * 取出开始日期和结束日期，以String数组形式返回
     * @param dateBefore
     * @return
     */
    public String[] handleDate(String dateBefore){
        dateBefore = dateBefore.replace("[","");
        dateBefore = dateBefore.replace("]","");
        return dateBefore.split(",");
    }

}
